package database;
import java.sql.*;
import java.util.HashSet;

import communicator.GlobalObjectId;
import communicator.LocalObjectId;
import communicator.PortNumber;

import hypeerweb.SimplifiedNodeDomain;


/**
*HyPeerWebDatabase:--Interfaces with the database
*
* @authors Joshua Lepinski <joshualepinski@gmail.com>,BJ Nordgren <bj_nordgren@hotmail.com>       
*
*/
public class HyPeerWebDatabase
{
    private static HyPeerWebDatabase singleton = null;
    public static final String DEFAULT_DATABASE_NAME="HyPeerWeb.db";// sets up the default database name
    public static String dbName=DEFAULT_DATABASE_NAME;// declares the current database name
        
      /**
         * constructs the database 
         * @authors Joshua Lepinski <joshualepinski@gmail.com>,BJ Nordgren <bj_nordgren@hotmail.com>
         * @param arg none
         * @exception throws any exception
         * @return HypeerWebDatabase Object
         */ 
    private HyPeerWebDatabase() throws Exception // bleah this is my comment
    {
            Class.forName("org.sqlite.JDBC");// states which database service we are using
        
          
          
          Connection conn = DriverManager.getConnection("jdbc:sqlite:"+dbName);// creates a connection to the database or creates it if it's not there
          Statement stat = conn.createStatement();
          
          // a series of statements to create the tables in our database
          stat.executeUpdate("create table if not exists Node(WebID integer primary key, H integer,State integer,Fold integer,SFold integer, ISFold integer);");
          stat.executeUpdate("create table if not exists Neighbor(Neighbor1 integer , Neighbor2 integer,PRIMARY KEY (Neighbor1,Neighbor2));");
          stat.executeUpdate("create table if not exists SurrogateNeighbor(SNeighbor integer, ISNeighbor integer,PRIMARY KEY (SNeighbor,ISNeighbor));");
          stat.executeUpdate("create table if not exists ProxyNode(WebID integer primary key, LocalID integer , PortNum integer, IPAddress integer);");
          
          
          conn.close();//closes the connection to the database
        
    }
     /**
         * Initializes the Database with default name 
         * @authors Joshua Lepinski <joshualepinski@gmail.com>,BJ Nordgren <bj_nordgren@hotmail.com>
         * @param none
         * @return void
         */
    public static void initHyPeerWebDatabase(){
        initHyPeerWebDatabase(null);
    }
   /**
         * Initializes the Database with the given string as the name
         * @authors Joshua Lepinski <joshualepinski@gmail.com>,BJ Nordgren <bj_nordgren@hotmail.com>
         * @param string (serves as the name for the database)
         * @return void
         */
    public static void initHyPeerWebDatabase(String name)
    {
          /* this sets the name of the database to the default name 
           * or current database name if a name is not sufficiently provided
       */
          
          if(name == null || name.equals(""))
              dbName = DEFAULT_DATABASE_NAME;  
          else
              dbName = name;
      
          try
          {
            singleton = new HyPeerWebDatabase(); // we initialize the HyPeerWeb Database inside the constructor
          }
          catch (Exception e) 
          {
            e.printStackTrace();
          }
              
    }
     /**
         * Returns a requested node from the database
         * @authors Joshua Lepinski <joshualepinski@gmail.com>,BJ Nordgren <bj_nordgren@hotmail.com>
         * @param int (the requested Node's webID)
         * @exception throws any exception
         * @return SimplifiedNodeDomain
         */
    public SimplifiedNodeDomain getNode(int webID) throws Exception
    {
          //here we declare variables for parameters to the simplified node domain
        int height = -1;
        HashSet<Integer> neighbors = new HashSet<Integer>();
        HashSet<Integer> upPointers = new HashSet<Integer>();
        HashSet<Integer> downPointers = new HashSet<Integer>();
        int fold = -1;
        int surrogateFold = -1;
        int inverseSurrogateFold = -1;
        int state = -1;
        
      
          Class.forName("org.sqlite.JDBC"); // states which database service we are using
          
          
          Connection conn = DriverManager.getConnection("jdbc:sqlite:"+dbName); // creates a connection to the database
          
          Statement stat = conn.createStatement();
          
          // pulls the table as a result set to pull the node information with the provided webID
          ResultSet rs = stat.executeQuery("select * from Node n where n.WebId = "+webID);
          boolean exists = false;
          while(rs.next())
          {
              exists = true;
              state = rs.getInt("State");
              height = rs.getInt("H");
              fold = rs.getInt("Fold");
              surrogateFold = rs.getInt("SFold");
              inverseSurrogateFold = rs.getInt("ISFold");
          }
          if(!exists)
              webID=-1;
          
          rs.close();
          
          /* pulls the table as a result set to pull out all the neighbor webID
           * to simplify this method we add every webID associated with the 
           * node we are getting and then removes the webID of the node we are getting*/
          ResultSet rsNeighbors = stat.executeQuery("select * from Neighbor n where n.Neighbor1 = "+webID+ " or n.Neighbor2 ="+ webID);
          
          while(rsNeighbors.next())
          {   
              neighbors.add(rsNeighbors.getInt("Neighbor1"));
              neighbors.add(rsNeighbors.getInt("Neighbor2"));
          }
          neighbors.remove(webID);
          
          
          rsNeighbors.close();
          /*this does the same thing as the previous section getting the Neighbors but with the
           * two different sets of ID's being upPointers and downPointers*/
          ResultSet rsSNeighbors=stat.executeQuery("select * from SurrogateNeighbor n where n.SNeighbor = "+webID+ " or n.ISNeighbor ="+ webID);
          
          while(rsSNeighbors.next())
          {
              upPointers.add(rsSNeighbors.getInt("SNeighbor")); 
              downPointers.add(rsSNeighbors.getInt("ISNeighbor"));
          }
          upPointers.remove(webID);
          downPointers.remove(webID);
         
          
          SimplifiedNodeDomain snd = new SimplifiedNodeDomain
                  (webID, height, neighbors, upPointers, downPointers, 
                          fold, surrogateFold, inverseSurrogateFold, state);
          rsSNeighbors.close();
         
          
          conn.close();
          return snd;
          
    }
      /**
         * Returns a HyPeerWebDatabase 
         * @authors Joshua Lepinski <joshualepinski@gmail.com>,BJ Nordgren <bj_nordgren@hotmail.com>
         * @param none
         * @return a HyPeerWebDatabase
         */
    public static HyPeerWebDatabase getSingleton()
    {
        assert(singleton != null);
        return singleton;
    
    }
    /**
         * adds a Node to the HyPeerWebDatabase
         * @authors Joshua Lepinski <joshualepinski@gmail.com>,BJ Nordgren <bj_nordgren@hotmail.com>
         * @param SimplifiedNodeDomain
         * @return void
         */ 
  public void addNodeToDatabase(SimplifiedNodeDomain snd) 
    {
          int webID = snd.getWebId();
          int height = snd.getHeight();
          int state = snd.getState();
        HashSet<Integer> neighbors = snd.getNeighbors();
        HashSet<Integer> upPointers = snd.getUpPointers();
        HashSet<Integer> downPointers = snd.getDownPointers();
        int fold = snd.getFold();
        int surrogateFold = snd.getSurrogateFold();
        int inverseSurrogateFold = snd.getInverseSurrogateFold(); 
        
        
          try 
          {
            Class.forName("org.sqlite.JDBC");// states which database service we are using
        
          // creates a connection to the database or creates it if it's not there
          Connection conn = DriverManager.getConnection("jdbc:sqlite:"+dbName);
          Statement stat = conn.createStatement();
          stat.executeUpdate("insert into Node values " +
                "( "+webID+", "+height+", "+state+", "+fold+", "+surrogateFold+", "+inverseSurrogateFold+" );");
          for(int neighbor: neighbors)
          {
              ResultSet rs = stat.executeQuery("select * from Neighbor where Neighbor1 = " +neighbor+" and Neighbor2 = "+webID+";");
            
              if(!rs.next())
                  stat.executeUpdate("insert into Neighbor values ("+webID+"," +neighbor+");");
    
              rs.close();
          }
          
          for(int sNeighbor : upPointers)
          {
              ResultSet rs = stat.executeQuery("select * from SurrogateNeighbor where SNeighbor = " +sNeighbor+" and ISNeighbor = "+webID+";");
             
              if(!rs.next())
              stat.executeUpdate("insert into SurrogateNeighbor values ("+sNeighbor+", "+webID+");");
    
              rs.close();
          }
          
          for(int isNeighbor: downPointers)
          {
            ResultSet rs = stat.executeQuery("select * from SurrogateNeighbor where SNeighbor = " +webID+" and ISNeighbor = "+isNeighbor+";");
            
            if(!rs.next())
                stat.executeUpdate("insert into SurrogateNeighbor values ("+webID+", "+isNeighbor+");");
    
            rs.close(); 
          }
          
          conn.close();
        }
        catch (Exception e) 
        {
          
            e.printStackTrace();
        }
          
    }
    /**
         *Completely removes the tables from the database and reinstantiates them as empty 
         * @authors Joshua Lepinski <joshualepinski@gmail.com>,BJ Nordgren <bj_nordgren@hotmail.com>
         * @param none
         * @return void
         */
    public void clear()
    {
          
          try
          {
              Class.forName("org.sqlite.JDBC");// states which database service we are using
          
              // creates a connection to the database or creates it if it's not there
              Connection conn = DriverManager.getConnection("jdbc:sqlite:"+dbName);
              Statement stat = conn.createStatement();
            
              stat.executeUpdate("drop table if exists Node;");
              stat.executeUpdate("drop table if exists Neighbor;");
              stat.executeUpdate("drop table if exists SurrogateNeighbor;");
              stat.executeUpdate("drop table if exists ProxyNode;");
              
              stat.executeUpdate("create table  Node(WebID integer primary key, H integer,State integer,Fold integer,SFold integer, ISFold integer);");
              stat.executeUpdate("create table  Neighbor(Neighbor1 integer , Neighbor2 integer,PRIMARY KEY (Neighbor1,Neighbor2));");
              stat.executeUpdate("create table  SurrogateNeighbor(SNeighbor integer, ISNeighbor integer,PRIMARY KEY (SNeighbor,ISNeighbor));");
              stat.executeUpdate("create table if not exists ProxyNode(WebID integer primary key, LocalID integer, PortNum integer, IPAddress integer);");
              
              
              conn.close();
          }
          catch (Exception e) 
          {
            e.printStackTrace();
          }
    }
    public void addProxyNodeToDatabase(GlobalObjectId id,int webId)
    {

        try 
        {
          Class.forName("org.sqlite.JDBC");// states which database service we are using
      
        // creates a connection to the database or creates it if it's not there
        Connection conn = DriverManager.getConnection("jdbc:sqlite:"+dbName);
        Statement stat = conn.createStatement();
        stat.executeUpdate("insert into ProxyNode values " +
              "( "+webId+", "+id.getLocalObjectId()+", "+id.getPortNumber()+", "+id.getMachineAddr()+" );");
       
        
        conn.close();
      }
      catch (Exception e) 
      {
        
          e.printStackTrace();
      }
        
    }
    public GlobalObjectId getNodeProxy(int webID)
    {
        int localObjectId=-1;
        String ipAddress="";
        int port=-1;
        
        try 
        { 
            Class.forName("org.sqlite.JDBC");
       
        
        Connection conn = DriverManager.getConnection("jdbc:sqlite:"+dbName); // creates a connection to the database
        
        Statement stat = conn.createStatement();
        
        // pulls the table as a result set to pull the node information with the provided webID
        ResultSet rs = stat.executeQuery("select * from ProxyNode n where n.WebId = "+webID);
        boolean exists = false;
        while(rs.next())
        {
            exists = true;
            localObjectId = rs.getInt("LocalID");
            port = rs.getInt("PortNum");
            ipAddress = rs.getString("IPAddress");

          
        }
        if(!exists)
            webID=-1;
        
        rs.close();
        conn.close();
        }
        catch (Exception e) 
        {
          
            e.printStackTrace();
        }
        LocalObjectId loid= new LocalObjectId(localObjectId);
        PortNumber p=new PortNumber(port);
        GlobalObjectId gloid=new GlobalObjectId(ipAddress,p,loid);
        return gloid;
    }
    
}